
do "E:\yungyu\lottery_baby\do\set_environment.do"

clear
set more off

cap program drop summary_stat
program define summary_stat

syntax [if] [in] [aw fw iw pw], col(string) save(string)

	preserve

	marksample touse
	qui keep if `touse'

	collapse 	(mean)age (sd)age_sd = age ///
				(mean)resident_urban (sd)resident_urban_sd = resident_urban ///
				(mean)female (sd)female_sd = female ///
				(mean)married (sd)married_sd = married ///
				(mean)pers_labor (sd)pers_labor_sd = pers_labor ///
				(mean)pers_earnings (sd)pers_earnings_sd = pers_earnings ///
				(mean)pers_income (sd)pers_income_sd = pers_income ///	
				(mean)pers_asset (sd)pers_asset_sd = pers_asset ///
				(mean)pers_liquidasset (sd)pers_liquidasset_sd = pers_liquidasset ///
				(mean)pers_saving (sd)pers_saving_sd = pers_saving ///
				(mean)tot_earnings (sd)tot_earnings_sd = tot_earnings ///
				(mean)tot_income (sd)tot_income_sd = tot_income ///	
				(mean)tot_asset (sd)tot_asset_sd = tot_asset ///
				(mean)tot_liquidasset (sd)tot_liquidasset_sd = tot_liquidasset ///
				(mean)tot_saving (sd)tot_saving_sd = tot_saving ///						
				(mean)totbaby (sd)totbaby_sd = totbaby ///
				(mean)pre1born (sd)pre1born_sd = pre1born ///
				(mean)pre2born (sd)pre2born_sd = pre2born ///
				(mean)pre3born (sd)pre3born_sd = pre3born ///
				(count)N = n [aw=w]
	
	xpose, clear v
	order _var
	rename v1 `col'
	rename _var variables
	tostring `col', replace force format(%15.3fc)
	replace `col' = "(" + `col' + ")" if strpos(var,"_sd")~=0
	replace variables = "" if strpos(var,"_sd")~=0
	
	save "`save'", replace
	
	restore
end


use "$wdata/population.dta", clear

replace age = age - 1

foreach var in income earnings asset liquidasset saving{
	gen tot_`var' = pers_`var' + spouse_`var'
}

gen resident_urban = resident_t == 1 | resident_m == 1
gen female = gender == 2

gen prize10M = prize
gen anyprize = prize > 0
rename totbaby_pre1 totbaby

gen n = 1
gen w = 1

** Household variables
foreach var in saving{
	gen avg_`var' = pers_`var' if spouse_idn == ""
	replace avg_`var' = (pers_`var' + spouse_`var')/2 if spouse_idn ~= ""
}
		
gen pers_labor = pers_earnings > 0
gen avg_labor  = avg_earnings>0	
	
summary_stat, col(Population) save("$table/dta/Tab1_Population.dta")

use "$wdata\project_lottery_fertility_b3a6_first_5k.dta", clear

** Household variables	
foreach var in income earnings asset liquidasset saving{
	gen tot_`var' = pers_`var' + spouse_`var'
}

foreach var in saving{
	gen avg_`var' = pers_`var' if spouse_idn == ""
	replace avg_`var' = (pers_`var' + spouse_`var')/2 if spouse_idn ~= ""
}

forv i = 0(1)3{
	gen v1 = baby > 0 if norm == -`i'
	egen pre`i'born = mean(v1), by(houseid)
	drop v1
	gen v1 = pers_real_prize_aftertax if norm == -`i'
	egen pre`i'prize = mean(v1), by(houseid)
	drop v1
	recode pre`i'prize . = 0
}

gen resident_urban = resident_t == 1 | resident_m == 1
gen female = pers_gender == 2

gen pers_labor = pers_earnings > 0
gen avg_labor  = avg_earnings > 0	

gen prize = winyr_pers_real_prize_aftertax
replace prize = pre0prize if current == 0

gen anyprize = prize>0
	

foreach var in pers_asset{

	gen `var'_grp=.
	replace `var'_grp=0 if `var'<0
	replace `var'_grp=1 if `var'==0	
	replace `var'_grp=2 if `var'>0 		& `var'<=100 // 0-10萬
	replace `var'_grp=3 if `var'>100	& `var'<=500  // 10萬-50萬
	replace `var'_grp=4 if `var'>500	& `var'<=1000  // 50萬-100萬	
	replace `var'_grp=5 if `var'>1000	& `var'<=3000  // 100萬-200萬
	replace `var'_grp=6 if `var'>3000	& `var'<=5000  // 200萬-500萬	
	replace `var'_grp=7 if `var'>5000	& `var'<=10000   // 500萬-1000萬
	replace `var'_grp=8 if `var'>10000	  // 1000萬	
	tab `var'_grp
}


foreach var in pers_earnings{

	gen `var'_grp=.
	replace `var'_grp=1 if `var'<=0
	replace `var'_grp=2 if `var'>0 		& `var'<=100 // 0-10萬
	replace `var'_grp=3 if `var'>100	& `var'<=200  // 10萬-20萬	
	replace `var'_grp=4 if `var'>200	& `var'<=300  // 20萬-30萬
	replace `var'_grp=5 if `var'>300	& `var'<=500  // 30萬-50萬	
	replace `var'_grp=6 if `var'>500	& `var'<=800  // 50萬-80萬		
	replace `var'_grp=7 if `var'>800	& `var'<=1000  // 80萬-100萬
	replace `var'_grp=8 if `var'>1000	& `var'<=1500  // 100萬-150萬	
	replace `var'_grp=9 if `var'>1500   // 150萬以上
	tab `var'_grp
}
	
merge m:1 win_age pers_gender married pers_earnings_grp pers_asset_grp using "$wdata\reweight_earn_asset.dta"
drop if _m == 2
*drop _m

*** 把每個人的weight調整成pre1 同個人的weight在不同年要一樣)
bysort houseid : gen weight_pre1=weight if norm_year==-1
order weight weight_pre1, after(norm_year)
drop weight
bysort houseid : egen weight=sum(weight_pre1)
drop weight_pre1

cap gen n = 1
gen w = weight

summary_stat if norm_year == -1 & prize10M <= 5 , col(Weighted) save("$table/dta/Tab1_Weighted.dta")
	
use "$table/dta/Tab1.dta", clear
merge 1:1 _n using "$table/dta/Tab1_Weighted.dta", nogen
merge 1:1 _n using "$table/dta/Tab1_Population.dta", nogen

export excel "$table/Tables.xlsx", sheet("TabE2") sheetreplace first(var)
